/*
 * Created on 02-Oct-2006
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package wstrader.task.traffic.physical;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import wstrader.task.trade.TradeTask;
import pc.sql.value.BooleanValue;
import pc.sql.value.DateValue;
import pc.sql.value.IntValue;
import pc.sql.value.StringValue;
import pc.system.Tasks;

/**
 * @author NANSA
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class Contract extends TradeTask
{

	  public Contract(Tasks owner)
	  {
	    super(owner);
	  }
	
	  public long doTask()
	  {
	    String table_name = "contracts";
	    int task_id     = this.getRequest().getIntValue("task_id");

	    long focused_id = this.getRequest().getLongValue("contracts");
	    switch (task_id)
		{
	      case 1 : // Update

	        break;
	      case 2 : // Delete
	        focused_id = doDelete(table_name,focused_id);
	        focused_id = 1;
	        break;
	      default : // View only
	        break;
	    }
	    this.setFocusedID(focused_id);

	    return focused_id;
	  }  
	  
	  public ResultSet fetchContractList()
	  {
	  
	  		String CntType = getRequest().getStringValue("cnt_type");
	  		if (CntType.compareTo("")== 0) CntType = "0";
	  		int OriginId = getRequest().getIntValue("origin");
	  		int GradeId = getRequest().getIntValue("grade");	  	
	  		int BuySellId = getRequest().getIntValue("buyer_seller");
	  		java.sql.Date LsDt = getRequest().getValueDate("ls_date");
	  	
	  		int QualityId = getRequest().getIntValue("quality");
	  		String Sql="";
	  	
	  		Sql ="Select * from contracts where " +
			"(type = ? OR ?) " +
	        "and (origin_id = ? OR ?) "+
			"and (grade_id = ? OR ?) " +
			"and (supplier_buyer_id = ? OR ?) " +
			"and (quality_id = ? OR ?) " +
			"and ((last_date < ? OR ?) OR (last_date IS NULL)) " +
			"order by id desc";
	  		Vector vp = new Vector();

	  		vp.addElement(new StringValue(CntType));
	  		vp.addElement(new BooleanValue(CntType.compareTo("0") == 0));

	  		vp.addElement(new IntValue(OriginId));
	  		vp.addElement(new BooleanValue(OriginId == 0));

	  		vp.addElement(new IntValue(GradeId));
	  		vp.addElement(new BooleanValue(GradeId == 0));

	  		vp.addElement(new IntValue(BuySellId));
	  		vp.addElement(new BooleanValue(BuySellId == 0));

	  		vp.addElement(new IntValue(QualityId));
	  		vp.addElement(new BooleanValue(QualityId == 0));	  		
	
	  		vp.addElement(new DateValue(LsDt));
	  		vp.addElement(new BooleanValue(LsDt.compareTo(new java.sql.Date(0)) == 0));

	  		return getSQLCommand().executeQuery(Sql,vp);	  	
	  }
	  
	  public ResultSet getDeliveryInstruction_record()
	  {
	  	 int CntId = getRequest().getIntValue("contracts");
	  	 String Sql = "SELECT delivery_instruction.*,sum(weight_note.net_weight) as recdton,sum(weight_note.no_of_bags) as recdbag " +
		 			  "FROM delivery_instruction INNER JOIN weight_note ON (delivery_instruction.id=weight_note.inst_id) " +
					  "WHERE weight_note.type = 'I' and delivery_instruction.contract_id = " + CntId + " GROUP BY `weight_note`.`inst_id`";
	  	 ResultSet Rs = getSQLCommand().executeQuery(Sql);
	  	 try {
			Rs.beforeFirst();
			if (Rs.next()!= true)
			{
				Sql = "Select delivery_instruction.*, 0 as recdton, 0 as recdbag from delivery_instruction where contract_id = " + CntId;
				Rs = getSQLCommand().executeQuery(Sql);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	  	 
	  	 return Rs;
	  }
	  
	  public ResultSet getShippingInstruction_record()
	  {
	  	 int CntId = getRequest().getIntValue("contracts");
	  	 String Sql = "SELECT shipping_instruction.*,sum(weight_note_ex.net_weight) as shipton,sum(weight_note_ex.no_of_bags) as shipbag " +
		  "FROM shipping_instruction INNER JOIN weight_note_ex ON (shipping_instruction.id=weight_note_ex.inst_id) " +
		  "WHERE weight_note_ex.type = 'E' and shipping_instruction.contract_id = " + CntId + " GROUP BY `weight_note_ex`.`inst_id`";
	  	 
	  	 ResultSet Rs = getSQLCommand().executeQuery(Sql);
	  	 try {
			Rs.beforeFirst();
			if (Rs.next()!= true)
			{
				Sql = "Select shipping_instruction.*, 0 as shipton, 0 as shipbag from shipping_instruction where contract_id = " + CntId;
				Rs = getSQLCommand().executeQuery(Sql);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	  	 
	  	 return Rs;
	  }	  
	  
	  public ResultSet getContractLsDt()
	  {
	  	return getSQLCommand().executeQuery("select distinct last_date from contracts where last_date IS NOT NULL order by last_date");
	  }		
	  
        public ResultSet getContractRefList()
        {
        	
        	String Sql="";
       		Sql ="select id,contract_no,ref_number,tons,receive_tons,unfixed_tons from contracts order by id";
    	  	return getSQLCommand().executeQuery(Sql);        	
        }	  
}
